package com.bodacn.keypoint;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class DBUtil
{
    public static Connection getSQLConnection()
    {
        Connection conn = null;
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e)
        {
            System.out.println("加载数据库引擎失败");
            e.printStackTrace();
        }
        try{
//             String connectDB = "jdbc:jtds:sqlserver://121.229.24.37;DatabaseName=BDDMSNew";
//            conn = DriverManager.getConnection(connectDB, "boda", "P@ssw0rd");

            String connectDB = "jdbc:jtds:sqlserver://"+MainApplication.ServerIP+";DatabaseName="+MainApplication.DBName;
            conn = DriverManager.getConnection(connectDB, MainApplication.UserName, MainApplication.Password);
        }
        catch (SQLException e)
        {
            System.out.println("创建数据库连接失败!");
            e.printStackTrace();
        }
        return conn;
    }

    public static JSONArray QuerySQL2JSON(String paramSQLCommand)
    {
        // json数组
        JSONArray array = new JSONArray();
        try
        {
            Connection conn = getSQLConnection();
            if (conn!=null) {
                System.out.println(paramSQLCommand+"-------->准备执行");
                String sqlcmd = paramSQLCommand;
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sqlcmd);
                //取字段相关信息;
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                //遍列数据;
                while (rs.next()) {
                    JSONObject jsonObj = new JSONObject();
                    // 遍历每一列
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        Object value = rs.getObject(columnName);
                        if (value==null) value="";
                        jsonObj.put(columnName, value);
                    }
                    array.put(jsonObj);
                }
                rs.close();
                stmt.close();
                conn.close();
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println(paramSQLCommand+"-------->异常");
            JSONObject jsonObj = new JSONObject();
            try {
                jsonObj.put("SQLExcept","SQL查询异常-A");
                jsonObj.put("SQLMessage",e.getMessage());
                array.put(jsonObj);
            } catch (JSONException e1) {
                e1.printStackTrace();
            }
        }catch (JSONException e){
            e.printStackTrace();
            JSONObject jsonObj = new JSONObject();
            try {
                jsonObj.put("SQLExcept","SQL查询异常-B");
                jsonObj.put("SQLMessage",e.getMessage());
                array.put(jsonObj);
            } catch (JSONException e1) {
                e1.printStackTrace();
            }
        }
        return array;
    }

    //打开数据库;
    public static ResultSet OpenSQL(String paramSQLCommand)
    {
        ResultSet rs=null;
        try
        {
            Connection conn = getSQLConnection();
            if (conn!=null) {
                System.out.println(paramSQLCommand+"-------->准备执行");
                String sqlcmd = paramSQLCommand;
                Statement stmt = conn.createStatement();
                rs = stmt.executeQuery(sqlcmd);
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println(paramSQLCommand+"-------->异常");
            JSONObject jsonObj = new JSONObject();
            try {
                jsonObj.put("SQLExcept","SQL查询异常-A");
                jsonObj.put("SQLMessage",e.getMessage());
            } catch (JSONException e1) {
                e1.printStackTrace();
            }
        }
        return rs;
    }

    public static boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0 ) {
                return true;
            }
        }
        catch (SQLException e) {
            return false;
        }

        return false;
    }

    public static void main(String[] args)
    {

    }
}
